package org.devocean.groupbuying.dao.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.devocean.groupbuying.dao.SerialKeyDAO;
import org.devocean.groupbuying.domain.Purchase;
import org.devocean.groupbuying.domain.SerialKey;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class SerialKeyDAOImpl implements SerialKeyDAO {

	@PersistenceContext
	private EntityManager em;

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.devocean.groupbuying.dao.impl.SerialKeyDAO#findAllSerialKeys()
	 */
	public List<SerialKey> findAllSerialKeys() {

		return em.createQuery("select s from SerialKey s", SerialKey.class)
				.getResultList();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.devocean.groupbuying.dao.impl.SerialKeyDAO#countSerialKey()
	 */
	public long countSerialKey() {
		return em.createQuery("select count(s) from SerialKey s", long.class)
				.getSingleResult();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.devocean.groupbuying.dao.impl.SerialKeyDAO#findSerialKeyById(java
	 * .lang.Integer)
	 */
	public SerialKey findSerialKeyById(Integer id) {
		return em.find(SerialKey.class, id);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.devocean.groupbuying.dao.impl.SerialKeyDAO#deleteSerialKey(int)
	 */
	public void deleteSerialKey(int id) {
		final SerialKey serialKey = em.find(SerialKey.class, id);
		if (serialKey != null) {
			em.remove(serialKey);
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.devocean.groupbuying.dao.impl.SerialKeyDAO#saveSerialKey(org.devocean
	 * .groupbuying.domain.SerialKey)
	 */
	public SerialKey saveSerialKey(SerialKey serialKey) {
		em.persist(serialKey);
		return serialKey;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.devocean.groupbuying.dao.impl.SerialKeyDAO#updateSerialKey(org.devocean
	 * .groupbuying.domain.SerialKey)
	 */
	public SerialKey updateSerialKey(SerialKey serialKey) {
		em.merge(serialKey);
		return serialKey;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.devocean.groupbuying.dao.impl.SerialKeyDAO#findValidSerialKey(java
	 * .lang.Integer)
	 */
	public SerialKey findValidSerialKey(String SerialKeyID) {

		final SerialKey serialKey = em.find(SerialKey.class, SerialKeyID);
		final Query query = em
				.createQuery(
						"select s from SerialKey s where s.serialKey.serialKeyID=:vid and s.valid=1 ",
						SerialKey.class);
		query.setParameter("vid", SerialKeyID);

		return (SerialKey) query.getResultList().get(0);
	}

	public List<SerialKey> findSerialKeyByCustomer(Integer customerID,Integer start,Integer limit) {
		return  em
				.createQuery("SELECT serial FROM SerialKey serial " +
						"INNER JOIN FETCH serial.item item " +
						"INNER JOIN FETCH item.purchase pu " +
						" WHERE pu.customer.customerID =:cid", SerialKey.class)
				.setParameter("cid", customerID).setFirstResult(start).setMaxResults(limit).getResultList();
		
	}
	
	public Long countSerialKeyByCustomer(Integer customerID){
		return  em
				.createQuery("SELECT COUNT(serial) FROM SerialKey serial " +
						"INNER JOIN serial.item item " +
						"INNER JOIN item.purchase pu " +
						" WHERE pu.customer.customerID =:cid", Long.class)
				.setParameter("cid", customerID).getSingleResult();
	}
	public List<SerialKey> findSerialKeyByCustomerByExpireDate(Integer customerID,Integer start,Integer limit) {
		return  em
				.createQuery("SELECT serial FROM SerialKey serial " +
						"INNER JOIN FETCH serial.item it " +
						"INNER JOIN FETCH serial.voucher v " +
						"INNER JOIN FETCH it.purchase pu " +
						" WHERE pu.customer.customerID =:cid AND serial.used = 0 " +
						"ORDER BY v.expireDate", SerialKey.class)
				.setParameter("cid", customerID).setFirstResult(start).setMaxResults(limit).getResultList();
		
	}
	public List<SerialKey> findSerialKeyByCustomerByPurchaseDate(Integer customerID,Integer start,Integer limit) {
		return  em
				.createQuery("SELECT serial FROM SerialKey serial " +
						"INNER JOIN FETCH serial.item it " +
						"INNER JOIN FETCH serial.voucher v " +
						"INNER JOIN FETCH it.purchase pu " +
						"WHERE pu.customer.customerID =:cid " +
						"ORDER BY pu.purchaseDate DESC", SerialKey.class)
				.setParameter("cid", customerID).setFirstResult(start).setMaxResults(limit).getResultList();
		
	}
	public Long countSerialKeyByCustomerByPurchaseDate(Integer customerID) {
		return  em
				.createQuery("SELECT COUNT(serial) FROM SerialKey serial " +
						"INNER JOIN  serial.item it " +
						"INNER JOIN  serial.voucher v " +
						"INNER JOIN  it.purchase pu " +
						" WHERE pu.customer.customerID =:cid", Long.class)
				.setParameter("cid", customerID).getSingleResult();
		
	}
	public Long countSerialKeyByCustomerByExpireDate(Integer customerID) {
		return  em
				.createQuery("SELECT COUNT(serial) FROM SerialKey serial " +
						"INNER JOIN serial.item it " +
						"INNER JOIN serial.voucher v " +
						"INNER JOIN it.purchase pu " +
						" WHERE pu.customer.customerID =:cid AND serial.used = 0 ", Long.class)
				.setParameter("cid", customerID).getSingleResult();
		
	}
	

}
